/**  
 * 文 件 名: MenuDAO.java  
 * 描    述：   
 * 版    权：Copyright (c)20012-2012 tongzhou All Rights Reserved.
 * 公    司: 同舟 
 * 作    者：彭定雄                     
 * 版    本: 1.0  
 * 创建时间: 2012-10-27
 *  
 * 修改历史：  
 * 时间                             作者                       版本                        描述  
 * ------------------------------------------------------------------  
 * 2012-10-27        彭定雄                1.0          1.0 Version  
 */       

/**
 * 
 */
package com.tongzhou.system.menu.dao.impl;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.tongzhou.db.DBUtil;
import com.tongzhou.db.exception.DBException;
import com.tongzhou.system.menu.dao.IMenuDAO;
import com.tongzhou.system.menu.model.Menu;
import com.tongzhou.system.menu.model.SearchMenuCondition;
import com.tongzhou.util.Util;

public class MenuDAO implements IMenuDAO{

	@Override
	public void addMenu(Menu menu) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("update sys_menu set sort=sort+1 ");
		sql.append("where parent_id='"+(Util.checkObjIsNull(menu.getParent_id())?"0":menu.getParent_id())+"' ");
		sql.append(" and sort>'"+menu.getSort()+"' ");
		DBUtil.update(sql.toString());
		
		sql.delete(0, sql.length());
		sql.append("insert into sys_menu(name,url,sort,parent_id,module_id,active) ");
		sql.append("values('"+menu.getName()+"','"+menu.getUrl()+"',");
		sql.append("'"+(Integer.valueOf(menu.getSort())+1)+"','"+(Util.checkObjIsNull(menu.getParent_id())?"0":menu.getParent_id())+"',");
		sql.append("'"+(Util.checkObjIsNull(menu.getModule_id())?"0":menu.getModule_id())+"','"+menu.getActive()+"');");
		DBUtil.update(sql.toString());
	}

	@Override
	public void deleteMenu(Menu menu) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("update sys_menu m inner join ");
		sql.append("(select m2.id from sys_menu m2 where m2.parent_id=(select parent_id from sys_menu where id='"+menu.getId()+"') ");
		sql.append(" and m2.sort>(select sort from sys_menu where id='"+menu.getId()+"')) m2");
		sql.append(" on m2.id=m.id ");
		sql.append(" set m.sort=m.sort-1; ");
		DBUtil.update(sql.toString());
		
		sql.delete(0, sql.length());
		sql.append("delete from sys_menu where id='"+menu.getId()+"'");
		DBUtil.update(sql.toString());
		
		sql.delete(0, sql.length());
		sql.append("delete from sys_menu where parent_id='"+menu.getId()+"'");
		DBUtil.update(sql.toString());
	}

	@Override
	public List<Menu> listMenu(SearchMenuCondition condition)
			throws DBException {
		List<Menu> result=null;
		//查询条件SQL
		//查询参数
		StringBuffer sqlCondition = new StringBuffer() ;
		List<String> paramList = new ArrayList<String>() ;
		
		if(!Util.checkObjIsNull(condition.getName())){
			sqlCondition.append(" and m.name like ?") ;
			paramList.add("%"+condition.getName()+"%") ;
		}
		if(!Util.checkObjIsNull(condition.getParent_name())){
			sqlCondition.append(" and m2.name like ?") ;
			paramList.add("%"+condition.getParent_name()+"%") ;
		}
		if(!Util.checkObjIsNull(condition.getActive())){
			sqlCondition.append(" and m.active=?") ;
			paramList.add(condition.getActive()) ;
		}
		
		//取得满足条件的总记录数和总页数
		StringBuffer sql = new StringBuffer() ;
		sql.append("select count(*) ");
		sql.append(" from sys_menu m left join sys_menu m2 on m2.id=m.parent_id ");
		sql.append(" where 1=1 ");
		sql.append(sqlCondition);
		
		//总记录数
		long total = DBUtil.getCount(sql.toString(),paramList.toArray()) ;
		//总页数
		long totalPage = Util.getTotalPage(total, condition.getPageSize());
		
		condition.setTotal(total);
		condition.setTotalPage(totalPage);
		
		//查询某页的列表数据
		sql.delete(0, sql.length());
		sql.append("select m.id,m.name,m.url,m.sort,m.parent_id,m2.name as parent_name,");
		sql.append("m.module_id,m.active ");
		sql.append(" from sys_menu m left join sys_menu m2 on m2.id=m.parent_id ");
		sql.append(" where 1=1 ");
		sql.append(sqlCondition);
		sql.append(" order by "+condition.getOrderBy()+" "+condition.getOrderByType()) ;
		result = DBUtil.query(sql.toString(), new BeanListHandler<Menu>(Menu.class), paramList.toArray(), Util.getStartPos(condition.getCurrentPage(), condition.getPageSize()), condition.getPageSize()) ;
		return result;
	}

	@Override
	public void updateMenu(Menu menu,Menu oldMenu) throws DBException {
		StringBuffer sql=new StringBuffer();
		if(Integer.valueOf(oldMenu.getSort())>=Integer.valueOf(menu.getSort())){
			menu.setSort(String.valueOf(Integer.valueOf(menu.getSort())+1));
		}
		if(!menu.getParent_id().equals(oldMenu.getParent_id())||!menu.getSort().equals(oldMenu.getSort())){
			//移动序号小于更改前的序号，再移动序号大于更改后的序号
			sql.append("update sys_menu set sort=sort-1 where parent_id='"+oldMenu.getParent_id()+"' and sort>='"+oldMenu.getSort()+"'");
			DBUtil.update(sql.toString());
			
			sql.delete(0, sql.length());
			sql.append("update sys_menu set sort=sort+1 where parent_id='"+menu.getParent_id()+"' and sort>='"+menu.getSort()+"'");
			DBUtil.update(sql.toString());
		}
		sql.delete(0, sql.length());
		sql.append("update sys_menu set ");
		sql.append("name='"+menu.getName()+"',url='"+menu.getUrl()+"',");
		sql.append("sort='"+menu.getSort()+"',parent_id='"+(Util.checkObjIsNull(menu.getParent_id())?"0":menu.getParent_id())+"',");
		sql.append("module_id='"+(Util.checkObjIsNull(menu.getModule_id())?"0":menu.getModule_id())+"',active='"+menu.getActive()+"' ");
		sql.append(" where id='"+menu.getId()+"' ");
		DBUtil.update(sql.toString());
	}

	@Override
	public List<Menu> getMenuList(Menu menu) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select m.id as parent_id,m.name,m.sort ");
		sql.append(" from sys_menu m ");
		sql.append(" where m.active='Y' and m.parent_id='0' ");
		if(!Util.checkObjIsNull(menu.getId())){
			sql.append(" and id!='"+menu.getId()+"'");
		}
		sql.append(" order by sort asc ");
		List<Menu> result=DBUtil.query(sql.toString(),new BeanListHandler<Menu>(Menu.class));
		return result;
	}

	@Override
	public List<Menu> getMenuListByParentID(Menu menu) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select m.sort,m.name,m.id ");
		sql.append(" from sys_menu m ");
		sql.append(" where m.active='Y' and m.parent_id='"+menu.getParent_id()+"' and id!='"+menu.getId()+"' order by m.sort asc");
		List<Menu> result=DBUtil.query(sql.toString(),new BeanListHandler<Menu>(Menu.class));
		return result;
	}
	
	@Override
	public List<Menu> checkMenuByName(Menu menu) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select name from sys_menu where name='"+menu.getName()+"' and id!='"+menu.getId()+"'");
		List<Menu> result=DBUtil.query(sql.toString(),new BeanListHandler<Menu>(Menu.class));
		return result;
	}

	@Override
	public Menu getMenuById(Menu menu) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select m.id,m.name,m.url,m.sort,m.parent_id,");
		sql.append("m.module_id,m.active ");
		sql.append(" from sys_menu m ");
		sql.append(" where 1=1 ");
		sql.append(" and id='"+menu.getId()+"'");
		Menu menu1=DBUtil.query(sql.toString(), new BeanHandler<Menu>(Menu.class));
		return menu1;
	}

	@Override
	public List<Menu> getSortMenuList(Menu menu) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select id,name,sort from sys_menu where parent_id='"+menu.getParent_id()+"' and id!='"+menu.getId()+"' order by sort asc ");
		List<Menu> result=DBUtil.query(sql.toString(),new BeanListHandler<Menu>(Menu.class));
		return result;
	}

	@Override
	public Menu getBeforeMenuById(Menu menu) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select max(m.sort) as sort ");
		sql.append(" from sys_menu m  ");
		sql.append(" where 1=1  and m.parent_id='"+menu.getParent_id()+"' ");
		sql.append(" and m.sort<'"+menu.getSort()+"';");
		Menu menu1=DBUtil.query(sql.toString(), new BeanHandler<Menu>(Menu.class));
		return menu1;
	}

	@Override
	public List<Menu> getSubMenuList(Menu menu) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select m.id,m.name,m.url,m.sort,m.parent_id,");
		sql.append("m.module_id,m.active ");
		sql.append(" from sys_menu m ");
		sql.append(" where m.parent_id='"+menu.getId()+"' and m.active='Y' ");
		sql.append(" order by m.sort asc ");
		List<Menu> result=DBUtil.query(sql.toString(),new BeanListHandler<Menu>(Menu.class));
		return result;
	}
	
	@Override
	public List<Menu> getSubMenuList2(Menu menu) throws DBException {
		int userID=Util.getAdminUserId();
		StringBuffer sql=new StringBuffer();
		sql.append("select distinct m.id,m.name,m.url,m.sort,m.parent_id,");
		sql.append("m.module_id,m.active ");
		sql.append(" from sys_menu m inner join sys_role_menu_relation r on r.menu_id=m.id ");
		sql.append(" inner join sys_role sr on sr.id=r.role_id ");
		sql.append(" inner join sys_user_role_relation su on su.role_id=r.role_id ");
		sql.append(" where m.parent_id='"+menu.getId()+"' and m.active='Y' and sr.active='Y' ");
		sql.append(" and su.user_id='"+userID+"'");
		sql.append(" order by m.sort asc ");
		List<Menu> result=DBUtil.query(sql.toString(),new BeanListHandler<Menu>(Menu.class));
		return result;
	}

	@Override
	public List<Menu> listMenu() throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select m.id,m.name,m.url,m.sort,m.parent_id,");
		sql.append("m.module_id,m.active ");
		sql.append(" from sys_menu m ");
		sql.append(" where m.parent_id='0' and m.active='Y' ");
		sql.append(" order by m.sort asc ");
		List<Menu> result=DBUtil.query(sql.toString(),new BeanListHandler<Menu>(Menu.class));
		return result;
	}
	
	@Override
	public List<Menu> listMenu2() throws DBException {
		int userID=Util.getAdminUserId();
		StringBuffer sql=new StringBuffer();
		sql.append("select distinct m.id,m.name,m.url,m.sort,m.parent_id,");
		sql.append("m.module_id,m.active ");
		sql.append(" from sys_menu m inner join sys_role_menu_relation r on r.menu_id=m.id ");
		sql.append(" inner join sys_role sr on sr.id=r.role_id ");
		sql.append(" inner join sys_user_role_relation su on su.role_id=r.role_id ");
		sql.append(" where m.parent_id='0' and m.active='Y' ");
		sql.append(" and su.user_id='"+userID+"' and sr.active='Y' ");
		sql.append(" order by m.sort asc ");
		List<Menu> result=DBUtil.query(sql.toString(),new BeanListHandler<Menu>(Menu.class));
		return result;
	}

}
